using System;
using System.Collections.Generic;
using System.Data;
using System.Globalization;
using System.Text;
using System.Reflection;
using Cobalt75.GreyMatter;
using GMID = System.Int64;
using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace Cobalt75.GreyMatter.MySql
{
   
    public class MySqlGMCommunicator : GMCommunicator
    {

        #region Constructors, Diposers & Initalisers
       
        public MySqlGMCommunicator(string database)
            : base(database)
        {
            Init("Persist Security Info=False;database=" + Database + ";server=localhost;user id=" + Database + ";password=" + Database + ";");
        }
        public MySqlGMCommunicator(string database, string server)
            : base(database)
        {
            Init("Persist Security Info=False;database=" + Database + ";server=" + server + ";user id=" + Database + ";password=" + Database + ";");
        }
        public MySqlGMCommunicator(string database, string server, string userName, string password)
            : base(database)
        {
            Init("Persist Security Info=False;database=" + Database + ";server=" + server + ";user id=" + userName + ";password=" + password + ";");
        }
        public MySqlGMCommunicator(string database, string userName, string password)
            : base(database)
        {
            Init("Server=.;Protocol=pipe;Persist Security Info=False;Database=" + Database + ";pipe=MySql;user id=" + userName + ";password=" + password + ";");
        }
        private void Init(string connectionString)
        {
            Connection = new MySqlConnection();
            
            //Connection.ConnectionString = new MySqlConnectionStringBuilder(connectionString).ToString();
            Connection.ConnectionString = connectionString;
           
            Connection.Open();
        }
        ~MySqlGMCommunicator()
        {
            Dispose(true);
        }
        #endregion
        #region GMQuery To MySql Sql commands

        public new const char QuoteChar = '`';
        public new const char ParameterChar = '?';



        protected override string ToString(GMQueryClause clause)
        {
            switch (clause.ClauseOperator)
            {
                case GMQueryClauseOperator.Equals:
                    return (ToString(clause.ItemA) + " = " + ToString(clause.ItemB));
                case GMQueryClauseOperator.GreaterThan:
                    return (ToString(clause.ItemA) + " > " + ToString(clause.ItemB));
                case GMQueryClauseOperator.IsNot:
                    return (ToString(clause.ItemA) + " != " + ToString(clause.ItemB));
                case GMQueryClauseOperator.LessThan:
                    return (ToString(clause.ItemA) + " < " + ToString(clause.ItemB));
                case GMQueryClauseOperator.Like:
                    return (ToString(clause.ItemA) + " LIKE " + ToString(clause.ItemB));
                case GMQueryClauseOperator.IsNotNull:
                    return (ToString(clause.ItemA) + " is not NULL");
                case GMQueryClauseOperator.IsNull:
                    return (ToString(clause.ItemA) + " is NULL");
                case GMQueryClauseOperator.FindInSet:
                    return ("FIND_IN_SET(" + ToString(clause.ItemA) + "," + ToString(clause.ItemB) + ")");
                case GMQueryClauseOperator.OtherFunction:
                    return (String.Format(CultureInfo.InvariantCulture, clause.OtherFunctionFormat, ToString(clause.ItemA), ToString(clause.ItemB)));

                default:
                    return "??";
            }
        }
        public override string GetFunctionFormatString(GMQuerySqlFunction function, params GMQueryClauseItemReference[] references)
        {
            switch (function)
            {
                case GMQuerySqlFunction.StringConcatenate:
                    return ("CONCAT("+ToString(references)+")");

                    

                default:
                    return ("?");
            }
        }
        public override IDbCommand CommandFromQuery(GMQuery query)
        {
            MySqlCommand com = new MySqlCommand();
            SetCommandFromQuery(query, com);
            return (com);
        }
        #endregion

        #region Other MySql Specific overrides
        public override IDataParameter CreateParameter(string paraName, object paraValue)
        {
            return new MySqlParameter(ParameterNameFromId(paraName),paraValue);

        }
        protected override GMID GetLastInsertId(IDbCommand command,string tableName)
        {
            command.CommandText = "SELECT LAST_INSERT_ID()";

            return (GMID)Convert.ChangeType(command.ExecuteScalar().ToString(), typeof(GMID),CultureInfo.InvariantCulture);
        }
        protected override object DBToClr(object obj, GMColumnBaseAttribute attr)
        {
            if (attr == null)
            {
                return obj; //For ID lookup
            }
            object v = obj;
            Type t = attr.ClrType;
            if (attr.IsPrimary)
            {
                return v;
            }
            if (attr.ClrType.IsGenericType)
            {
                
                Type ta = attr.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb && v==DBNull.Value)
                {
                    return ((GMID)0);
                }
                return v;

            }
            if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            ;
            if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                if (t == typeof(Int16))
                {

                    return v;
                }
                if (t == typeof(Int32))
                {

                    return v;
                }
                if (t == typeof(Int64))
                {

                    return v;
                }
                if (t.IsEnum)
                {

                    if (v == null)
                    {
                        return null;
                    }
                    return Enum.Parse(t, v.ToString());
                }
                if (t == typeof(UInt16))
                {

                    return v;
                }
                if (t == typeof(UInt32))
                {

                    return v;
                }
                if (t == typeof(UInt64))
                {

                    return v;
                }
                if (t == typeof(DateTime) && attr.Precision)
                {

                    if (v == null)
                    {
                        return null;
                    }
                    return new DateTime((long)v);
                }
                if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                if (t == typeof(char))
                {
                    if (v == null)
                    {
                        return null;
                    }
                    return v.ToString()[0];
                }
                if (t == typeof(decimal))
                {

                    return v;
                }
                if ( t == typeof(bool)&& v.GetType() == typeof(SByte))
                {
                    return (((SByte)v) == 1);
                }
                if (t == typeof(bool))
                {

                    return v;
                }
                if (t == typeof(Byte))
                {

                    return v;
                }
                if (t == typeof(SByte))
                {

                    return v;
                }
                if (t == typeof(float))
                {

                    return v;
                }
                if (t == typeof(double))
                {

                    return v;
                }
            }
            if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
                else
                {
                    return v;
                }
            }
            return (v);
        }
        protected override object ClrToDB(object obj, GMColumnBaseAttribute attr)
        {
            object v = attr.ClrField.GetValue(obj);
            Type t = attr.ClrType;
            if (attr.IsPrimary)
            {
                return v;
            }
            if (attr.ClrType.IsGenericType)
            {
                Type ta = attr.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    IGMRelation igm = (IGMRelation)v;
                    if (igm.Id == 0)
                    {
                        return (null);
                    }
                    //return (((GMRelation<GMTable>)v).Value.ID);
                    return igm.Id;
                }
                return v;

            }
            if (t.IsArray && t.IsValueType)
            {
                return v;
            }
            if (t.IsValueType)
            {
                if (t == typeof(GMID))
                {
                    return v;
                }
                if (t == typeof(Int16))
                {

                    return v;
                }
                if (t == typeof(Int32))
                {

                    return v;
                }
                if (t == typeof(Int64))
                {

                    return v;
                }
                if (t.IsEnum)
                {

                    return v;
                }
                if (t == typeof(UInt16))
                {

                    return v;
                }
                if (t == typeof(UInt32))
                {

                    return v;
                }
                if (t == typeof(UInt64))
                {

                    return v;
                }
                if (t == typeof(DateTime) && attr.Precision)
                {

                    return ((DateTime)v).Ticks;
                }
                if (t == typeof(DateTime) && !attr.Precision)
                {

                    return v;
                }
                if (t == typeof(char))
                {

                    return v.ToString();
                }
                if (t == typeof(decimal))
                {

                    return v;
                }
                if (t == typeof(bool))
                {

                    return v;
                }
                if (t == typeof(Byte))
                {

                    return v;
                }
                if (t == typeof(SByte))
                {

                    return v;
                }
                if (t == typeof(float))
                {

                    return v;
                }
                if (t == typeof(double))
                {
                    return v;
                }
            }
            if (t == typeof(string) || t.IsSubclassOf(typeof(string)))
            {
                if (attr.LengthType == GMColumnLength.Small)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Medium)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Large)
                {

                    return v;
                }
                if (attr.LengthType == GMColumnLength.Undefined)
                {

                    return v;
                }
            }
            return (v);
        }
        protected override void SetDatabaseSchemaField(Type type, ref GMColumnBaseAttribute attribute)
        {
            if (attribute.IsPrimary)
            {
                attribute.SetDatabase("BIGINT(20) ","0");
                return;
            }
            if (attribute.ClrType.IsGenericType)
            {
                Type ta = attribute.ClrType.GetGenericTypeDefinition();
                Type tb = typeof(GMRelation<>);

                if (ta == tb)
                {
                    attribute.SetDatabase("BIGINT(20) ", "0");
                    return;
                }
                attribute.SetDatabase("?", "?");
                return;

            }
            if (type.IsArray && type.IsValueType)
            {
                attribute.SetDatabase("?", "?");
                return;
            }
            if (type.IsValueType)
            {
                if (type == typeof(GMID))
                {
                    attribute.SetDatabase("BIGINT(20) ", "0");
                    return;
                }
                if (type == typeof(Int16))
                {

                    attribute.SetDatabase("SMALLINT", "0");
                    return;
                }
                if (type == typeof(Int32))
                {

                    attribute.SetDatabase("INT", "0");
                    return;
                }
                if (type == typeof(Int64))
                {

                    attribute.SetDatabase("BIGINT", "0");
                    return;
                }
                if (type.IsEnum)
                {

                    attribute.SetDatabase("BIGINT", "0");
                    return;
                }
                if (type == typeof(UInt16))
                {

                    attribute.SetDatabase("SMALLINT UNSIGNED", "0");
                    return;
                }
                if (type == typeof(UInt32))
                {

                    attribute.SetDatabase("INT UNSIGNED", "0");
                    return;
                }
                if (type == typeof(UInt64))
                {
                    
                    attribute.SetDatabase("BIGINT UNSIGNED", "0");
                    return;
                }
                if (type == typeof(DateTime) && attribute.Precision)
                {

                    attribute.SetDatabase("BIGINT(20)", "0");
                    return;
                }
                if (type == typeof(DateTime) && !attribute.Precision)
                {

                    attribute.SetDatabase("DATETIME", "0");
                    return;
                }
                if (type == typeof(char))
                {

                    attribute.SetDatabase("VARCHAR(1)", "");
                    return;
                }
                if (type == typeof(decimal))
                {

                    attribute.SetDatabase("DECIMAL(65,30)", "0");
                    return;
                }
                if (type == typeof(bool))
                {

                    attribute.SetDatabase("TINYINT(1)", "0");
                    return;
                }
                if (type == typeof(Byte))
                {

                    attribute.SetDatabase("TINYINT UNSIGNED", "0");
                    return;
                }
                if (type == typeof(SByte))
                {

                    attribute.SetDatabase("TINYINT", "0");
                    return;
                }
                if (type == typeof(float))
                {

                    attribute.SetDatabase("FLOAT", "0");
                    return;
                }
                if (type == typeof(double))
                {

                    attribute.SetDatabase("DOUBLE", "0");
                    return;
                }
            }
            if (type == typeof(string) || type.IsSubclassOf(typeof(string)))
            {
                if (attribute.LengthType == GMColumnLength.Small)
                {

                    attribute.SetDatabase("TINYTEXT", null);
                    return;
                }
                if (attribute.LengthType == GMColumnLength.Medium)
                {

                    attribute.SetDatabase("MEDIUMTEXT", null);
                    return;
                }
                if (attribute.LengthType == GMColumnLength.Large)
                {

                    attribute.SetDatabase("LONGTEXT", null);
                    return;
                }
                if (attribute.LengthType == GMColumnLength.Undefined)
                {

                    attribute.SetDatabase("TEXT", null);
                    return;
                }
                
                attribute.SetDatabase("CHAR(" + attribute.ColumnLength + ")", "");
                return;
                
            }
            attribute.SetDatabase("BLOB","");
            return;
        }
        #endregion
        #region DDL SQL
        public override string TempIdTableCreationSql()
        {
            return (String.Format(CultureInfo.InvariantCulture,@"
DROP TABLE IF EXISTS `{0}`.`tbl_ids`;
CREATE TABLE  `{0}`.`tbl_ids` (
  `col` bigint(20) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
", this.Database));
        }
        public override string CreateTableSql(GMLinkTable linkTableAttr)
        {
            
            StringBuilder sB = new StringBuilder();
            sB.AppendLine(String.Format(CultureInfo.InvariantCulture,"CREATE TABLE `{0}` (",
                linkTableAttr.Name));
            List<string> CreateLines = new List<string>();


            CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"`{0}_id` bigint NOT NULL",
                linkTableAttr.Master.TableName
                ));

            CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"`{0}_id` bigint NOT NULL",
                linkTableAttr.Relation.TableName));

            if (linkTableAttr.Index != null)
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"`{0}_id` bigint NOT NULL",
                    linkTableAttr.Index.TableName));
            }
            if (linkTableAttr.Parameter != null)
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"`{0}` {1}",
                    linkTableAttr.Parameter.ColumnName,
                    linkTableAttr.Parameter.DatabaseTypeId));

            }
            //CreateLines.Add(String.Format());
            if (linkTableAttr.Unique && linkTableAttr.Index != null) //Only one relation per index.
            {
                CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (`{0}_id`,`{1}_id`)",
                    linkTableAttr.Master.TableName,
                    linkTableAttr.Index.TableName));

            }
            else if (!linkTableAttr.Duplicates) //More than one of the same relation with the same index.
            {
                if (linkTableAttr.Index != null)
                {
                    CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (`{0}_id`,`{1}_id`,`{2}_id`)",
                        linkTableAttr.Master.TableName,
                        linkTableAttr.Relation.TableName,
                        linkTableAttr.Index.TableName));
                }
                else
                {
                    CreateLines.Add(String.Format(CultureInfo.InvariantCulture,"PRIMARY KEY (`{0}_id`,`{1}_id`)", linkTableAttr.Master.TableName,
                        linkTableAttr.Relation.TableName));

                }
            }
            sB.AppendLine(String.Join("," + Environment.NewLine, CreateLines.ToArray()));
            sB.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=utf8 ");
            
            return sB.ToString();

        }
        static string[] CreateTableIndexes(GMTableAttribute tableAttr)
        {

            List<string> IndicesSQL = new List<string>();
            string unique;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.Key)
                {
                    if (kp.Value.Unique)
                    {
                        unique = "UNIQUE";
                    }
                    else
                    {
                        unique = "";
                    }
                    IndicesSQL.Add(
                        String.Format(CultureInfo.InvariantCulture,"CREATE {0} INDEX `key_{1}_{2}` ON `{1}`(`{2}`)",
                           unique,
                            tableAttr.TableName,
                            kp.Value.ColumnName
                        )
                        );

                }
            }
            return IndicesSQL.ToArray();
        }
        static string[] CreateTableIndexes(GMLinkTable linkTableAttr)
        {

            List<string> IndicesSQL = new List<string>();
            IndicesSQL.Add(
            String.Format(CultureInfo.InvariantCulture,"CREATE INDEX `k{0}{1}` ON `{0}`(`{2}_id`)",

                linkTableAttr.Name,
                linkTableAttr.Master.TableId,
                linkTableAttr.Master.TableName
                ));
            IndicesSQL.Add(
            String.Format(CultureInfo.InvariantCulture,"CREATE INDEX `k{0}{1}` ON `{0}`(`{2}_id`)",

                linkTableAttr.Name,
                linkTableAttr.Relation.TableId,
                linkTableAttr.Relation.TableName
                ));

            if (linkTableAttr.Index != null)
            {
                
            IndicesSQL.Add(
            String.Format(CultureInfo.InvariantCulture,"CREATE INDEX `k{0}{1}` ON `{0}`(`{2}_id`)",

                linkTableAttr.Name,
                linkTableAttr.Index.TableId,
                linkTableAttr.Index.TableName
                ));
            }
            


            return IndicesSQL.ToArray();
        }
        static string[] CreateTableForeignKeys(GMTableAttribute tableAttr)
        {

            List<string> ConstraintsSQL = new List<string>();



            StringBuilder ConstraintBuilder = new StringBuilder();
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.GMRelationType != null && kp.Value.IsDatabaseColumn)
                {
                    ConstraintBuilder.Length = 0;
                    ConstraintBuilder.AppendFormat("ALTER TABLE `{0}` ADD CONSTRAINT `{3}{1}_fk_{2}`",
                        tableAttr.TableName,
                        tableAttr.TableId,
                        kp.Value.GMRelationAttr.TableId,
                        kp.Value.ColumnPrefix);
                    ConstraintBuilder.AppendLine();
                    ConstraintBuilder.AppendFormat("   FOREIGN KEY (`{0}`) REFERENCES `{1}` (`id`)",
                        kp.Value.ColumnName,kp.Value.GMRelationAttr.TableName);
                    
                    ConstraintBuilder.AppendLine();
                    ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
                    ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");

                    ConstraintsSQL.Add(ConstraintBuilder.ToString());
                }
            }
            return ConstraintsSQL.ToArray();
        }
        static string[] CreateTableForeignKeys(GMLinkTable linkTableAttr)
        {

            string fkeylinktblname = linkTableAttr.Name;
            /*
            if (fkeylinktblname.Length > 32)
            {
                fkeylinktblname = "";
                foreach (string str in ltbl.Name.Split('_'))
                {
                    fkeylinktblname += str.Substring(0, Math.Min(3, str.Length));

                }
            }*/
            List<string> ConstraintsSQL = new List<string>();


            StringBuilder ConstraintBuilder = new StringBuilder();

            ConstraintBuilder.Length = 0;
            ConstraintBuilder.AppendFormat("ALTER TABLE `{0}` ADD CONSTRAINT `{0}_fk_{1}`",
                fkeylinktblname,
                linkTableAttr.Master.TableId);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY (`{0}_id`) REFERENCES `{0}` (`id`)",
                linkTableAttr.Master.TableName);

            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            ConstraintsSQL.Add(ConstraintBuilder.ToString());

            ConstraintBuilder.Length = 0;


            ConstraintBuilder.AppendFormat("ALTER TABLE `{0}` ADD CONSTRAINT `{0}_fk_{1}`",
                fkeylinktblname,
                linkTableAttr.Relation.TableId);
            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendFormat("   FOREIGN KEY (`{0}_id`) REFERENCES `{0}` (`id`)",
                linkTableAttr.Relation.TableName);

            ConstraintBuilder.AppendLine();
            ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
            ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
            ConstraintsSQL.Add(ConstraintBuilder.ToString());

            if (linkTableAttr.Index != null)
            {

                ConstraintBuilder.Length = 0;


                ConstraintBuilder.AppendFormat("ALTER TABLE `{0}` ADD CONSTRAINT `{0}_fk_{1}`",
                    fkeylinktblname,
                    linkTableAttr.Index.TableId);
                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendFormat("   FOREIGN KEY (`{0}_id`) REFERENCES `{0}` (`id`)",
                    linkTableAttr.Index.TableName);

                ConstraintBuilder.AppendLine();
                ConstraintBuilder.AppendLine("      ON UPDATE CASCADE");
                ConstraintBuilder.AppendLine("      ON DELETE RESTRICT");
                ConstraintsSQL.Add(ConstraintBuilder.ToString());
            }



            return ConstraintsSQL.ToArray();
        }
        public override string CreateTableSql(GMTableAttribute tableAttr)
        {
            StringBuilder sB = new StringBuilder();
            sB.AppendLine(String.Format(CultureInfo.InvariantCulture,"CREATE TABLE  `{0}` (",
                tableAttr.TableName));
            List<string> CreateLines = new List<string>();
            string nullDef;
            string autoIncrementDef;
            string defaultDef;
            foreach (KeyValuePair<FieldInfo, GMColumnBaseAttribute> kp in tableAttr.Columns)
            {
                if (kp.Value.IsDatabaseColumn)
                {
                    if (kp.Value.AllowNull)
                    {
                        nullDef = "";
                    }
                    else
                    {
                        nullDef = "NOT NULL";
                    }
                    if (kp.Value.IsPrimary)
                    {
                        autoIncrementDef = "AUTO_INCREMENT";
                        defaultDef = "";
                    }
                    else
                    {
                        autoIncrementDef = "";
                        defaultDef = "";
                    }


                    CreateLines.Add(
                        String.Format(CultureInfo.InvariantCulture,"   `{0}` {1} {2} {3} {4}",
                            kp.Value.ColumnName,
                            kp.Value.DatabaseTypeId,
                            nullDef,
                            autoIncrementDef,
                            defaultDef
                        )
                        );
                }
            }
            CreateLines.Add("PRIMARY KEY (`id`)");

            sB.AppendLine(String.Join("," + Environment.NewLine, CreateLines.ToArray()));
            sB.AppendLine(") ENGINE=InnoDB DEFAULT CHARSET=utf8");

 


  
            


            return sB.ToString();

        }
        
        
        public string TableSyncSql()
        {
            return String.Format(CultureInfo.InvariantCulture, @"
CREATE TABLE `{0}`.`tablesync` (
  `tablename` varchar(255) NOT NULL default '',
  `modifiedticks` bigint(20) NOT NULL default '00000000000000000000',
  PRIMARY KEY  (`tablename`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8", this.Database);
        }
        public override void CreateDBSchema()
        {
            Console.WriteLine("DB Schema (designed for MySql5+)");
            //First create the main tables
            //(In order of priority)
            SortedDictionary<int, GMTableAttribute> SORTtabAttrs = new SortedDictionary<int, GMTableAttribute>();

            foreach (KeyValuePair<Type, GMTableAttribute> kp in ReflectionCache)
            {
                SORTtabAttrs.Add(kp.Value.Priority, kp.Value);
            }
            GMCommandList gmcl = new GMCommandList(Connection.CreateCommand());
            gmcl.CommandSeparator = ";"+Environment.NewLine;
            gmcl.ExecuteAsAdded = true;
            gmcl.Add("USE mysql");

            gmcl.Add("set foreign_key_checks = 0");

            gmcl.Add("DROP DATABASE IF EXISTS {0}",
                this.Database);


            gmcl.Add("CREATE DATABASE {0}",
                this.Database);


            gmcl.Add("USE {0}",
                this.Database);


            gmcl.Add("DROP TABLE IF EXISTS `{0}`.`tablesync`",
                this.Database);



            gmcl.Add(TableSyncSql());


            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {

                gmcl.Add("INSERT IGNORE INTO `{0}`.`tablesync` VALUES ('{1}','0')",
                    Database,
                    tableAttr.TableName);
                gmcl.Add("DROP TABLE IF EXISTS `{0}`.`{1}`",
                    Database,
                    tableAttr.TableName);

                gmcl.Add(CreateTableSql(tableAttr));

            }


            //Then create the link tables

            foreach (GMLinkTable ltbl in LinkTables)
            {

                gmcl.Add("INSERT IGNORE INTO `{0}`.`tablesync` VALUES ('{1}','0')",
                    Database,
                    ltbl.Name);

                gmcl.Add("DROP TABLE IF EXISTS `{0}`.`{1}`",
                    Database,
                    ltbl.Name);

                gmcl.Add(CreateTableSql(ltbl));

                

            }

            string[] commands;
            //Create indicies
            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                commands = CreateTableIndexes(tableAttr);
                foreach (string comstr in commands)
                {
                    gmcl.Add(comstr);
                }
            }
            foreach (GMLinkTable ltbl in LinkTables)
            {
                commands = CreateTableIndexes(ltbl);
                foreach (string comstr in commands)
                {
                    gmcl.Add(comstr);
                }
            }
            //Forigen Keys
            foreach (GMTableAttribute tableAttr in SORTtabAttrs.Values)
            {
                commands = CreateTableForeignKeys(tableAttr);
                foreach (string comstr in commands)
                {
                    gmcl.Add(comstr);
                }
            }
            //Then create the link tables

            foreach (GMLinkTable ltbl in LinkTables)
            {

                commands = CreateTableForeignKeys(ltbl);
                foreach (string comstr in commands)
                {
                    gmcl.Add(comstr);
                }
            }


            gmcl.Add("set foreign_key_checks = 1");

            gmcl.End();
            Console.WriteLine("Create DB committed");



        }
        #endregion
    }
}
